from models import execute_query, execute_insert_commit
import logging

class SQLOperations:
    @staticmethod
    def get_course_categories():
        query = 'SELECT id, category_name, description FROM course.course_categories'
        result = execute_query(query, fetch=True)
        if result:
            categories = [
                {
                    'id': row[0],
                    'category_name': row[1],
                    'description': row[2]
                }
                for row in result
            ]
            return categories
        else:
            return []

    @staticmethod
    def get_course_detail(course_id):
        query = 'SELECT id, title, description, price, category_id, created_at, updated_at FROM course.courses WHERE id = %s'
        result = execute_query(query, (course_id,), fetch=True)
        if result:
            course = {
                'id': result[0][0],
                'title': result[0][1],
                'description': result[0][2],
                'price': result[0][3],
                'category_id': result[0][4],
                'created_at': result[0][5],
                'updated_at': result[0][6]
            }
            return course
        else:
            return None

    @staticmethod
    def create_order(user_id, course_id,order_id, status):
        logging.info('create_order: user_id={}, course_id={}'.format(user_id, course_id))
        query = 'INSERT INTO course.orders (user_id, course_id,order_id, order_status, created_at, updated_at) ' \
                'VALUES (%s, %s, %s, %s, NOW(), NOW()) ' \
                'RETURNING id, user_id,order_id, course_id,order_status, created_at, updated_at'
        try:
            result = execute_insert_commit(query, (user_id, course_id,order_id, status), fetch=True, commit=True)
            logging.debug('order:{}'.format(result))
            if result:
                order = {
                    'id': result[0][0],
                    'user_id': result[0][1],
                    'course_id': result[0][2],
                    'order_id': result[0][3],
                    'order_status': result[0][4],
                    'created_at': result[0][5],
                    'updated_at': result[0][6]
                }
                return order
            else:
                return None
        except Exception as e:
            logging.error(f"Failed to create order for user_id: {user_id}, course_id: {course_id} Error: {e}")
            raise